

/*
	Base Data Access Layer.
	(C) Lehavi Solutions	

*/
	
#region using	
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
#endregion


namespace DAL
{	
	
	public partial class Accounts
	{

		#region Basic CRUD
		// Create / Update / Delete
		protected internal static int Insert(BO.Account account)
		{
			var cmd =  @"
				insert into [Accounts]
					([ForeignID], [Name], [FBKey], [FBSecret], [TwKey], [TwSecret])
				values
					(@ForeignID, @Name, @FBKey, @FBSecret, @TwKey, @TwSecret);
				
				select scope_identity();
				";
				
			var parameters = new SqlParameter[6]; 
			
			if (account.ForeignID == null)
				parameters[0] = new SqlParameter("ForeignID",DBNull.Value);
			else
				parameters[0] = new SqlParameter("ForeignID",account.ForeignID);
			if (account.Name == null)
				parameters[1] = new SqlParameter("Name",DBNull.Value);
			else
				parameters[1] = new SqlParameter("Name",account.Name);
			if (account.FBKey == null)
				parameters[2] = new SqlParameter("FBKey",DBNull.Value);
			else
				parameters[2] = new SqlParameter("FBKey",account.FBKey);
			if (account.FBSecret == null)
				parameters[3] = new SqlParameter("FBSecret",DBNull.Value);
			else
				parameters[3] = new SqlParameter("FBSecret",account.FBSecret);
			if (account.TwKey == null)
				parameters[4] = new SqlParameter("TwKey",DBNull.Value);
			else
				parameters[4] = new SqlParameter("TwKey",account.TwKey);
			if (account.TwSecret == null)
				parameters[5] = new SqlParameter("TwSecret",DBNull.Value);
			else
				parameters[5] = new SqlParameter("TwSecret",account.TwSecret);
			
			return Convert.ToInt32(DBHelper.GetScalar(cmd, CommandType.Text, parameters));
		}
		
		protected internal static void Update(BO.Account account)
		{
			var cmd = @"
				update [Accounts] set
					ForeignID = @ForeignID, Name = @Name, FBKey = @FBKey, FBSecret = @FBSecret, TwKey = @TwKey, TwSecret = @TwSecret
				where ID = @ID
				";
				
			var parameters = new SqlParameter[7];
			parameters[0] = new SqlParameter("ID",account.ID );
			if (account.ForeignID == null)
				parameters[1] = new SqlParameter("ForeignID",DBNull.Value);
			else
				parameters[1] = new SqlParameter("ForeignID",account.ForeignID);
			if (account.Name == null)
				parameters[2] = new SqlParameter("Name",DBNull.Value);
			else
				parameters[2] = new SqlParameter("Name",account.Name);
			if (account.FBKey == null)
				parameters[3] = new SqlParameter("FBKey",DBNull.Value);
			else
				parameters[3] = new SqlParameter("FBKey",account.FBKey);
			if (account.FBSecret == null)
				parameters[4] = new SqlParameter("FBSecret",DBNull.Value);
			else
				parameters[4] = new SqlParameter("FBSecret",account.FBSecret);
			if (account.TwKey == null)
				parameters[5] = new SqlParameter("TwKey",DBNull.Value);
			else
				parameters[5] = new SqlParameter("TwKey",account.TwKey);
			if (account.TwSecret == null)
				parameters[6] = new SqlParameter("TwSecret",DBNull.Value);
			else
				parameters[6] = new SqlParameter("TwSecret",account.TwSecret);
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, parameters);
		}
		
		protected internal static void Delete(object ID)
		{
			var cmd = "delete [Accounts] where ID = @ID";
			
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, new SqlParameter("ID", ID));
		}

		#endregion 
			
		internal static DataTable Select(string cmd, SqlParameter[] parameters)
		{
			return DBHelper.GetTable(cmd, parameters);
		}
		
		internal static int Count(string cmd, SqlParameter[] parameters)
		{
			return Convert.ToInt32(DBHelper.GetScalar(cmd, parameters));
		}
		
		internal static void SetValue(object id, string columnName, object value)
		{
			var cmd = string.Format("update [Accounts] set [{0}] = @Value where ID = @ID", columnName);
			
			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("Value", value ?? DBNull.Value), new SqlParameter("ID", id));
		}
		
		internal static void SafeDetach(object fkID, string columnName)
		{
			var cmd = string.Format("update [Accounts] set [{0}] = null where [{0}] = @FkID", columnName);

			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("FkID", fkID));
		}
	
	}
			
	
	public partial class FBPages
	{

		#region Basic CRUD
		// Create / Update / Delete
		protected internal static int Insert(BO.FBPage fBPage)
		{
			var cmd =  @"
				insert into [FBPages]
					([ForeignID], [Name], [Token])
				values
					(@ForeignID, @Name, @Token);
				
				select scope_identity();
				";
				
			var parameters = new SqlParameter[3]; 
			
			parameters[0] = new SqlParameter("ForeignID",fBPage.ForeignID );
			parameters[1] = new SqlParameter("Name",fBPage.Name );
			parameters[2] = new SqlParameter("Token",fBPage.Token );
			
			return Convert.ToInt32(DBHelper.GetScalar(cmd, CommandType.Text, parameters));
		}
		
		protected internal static void Update(BO.FBPage fBPage)
		{
			var cmd = @"
				update [FBPages] set
					ForeignID = @ForeignID, Name = @Name, Token = @Token
				where ID = @ID
				";
				
			var parameters = new SqlParameter[4];
			parameters[0] = new SqlParameter("ID",fBPage.ID );
			parameters[1] = new SqlParameter("ForeignID",fBPage.ForeignID );
			parameters[2] = new SqlParameter("Name",fBPage.Name );
			parameters[3] = new SqlParameter("Token",fBPage.Token );
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, parameters);
		}
		
		protected internal static void Delete(object ID)
		{
			var cmd = "delete [FBPages] where ID = @ID";
			
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, new SqlParameter("ID", ID));
		}

		#endregion 
			
		internal static DataTable Select(string cmd, SqlParameter[] parameters)
		{
			return DBHelper.GetTable(cmd, parameters);
		}
		
		internal static int Count(string cmd, SqlParameter[] parameters)
		{
			return Convert.ToInt32(DBHelper.GetScalar(cmd, parameters));
		}
		
		internal static void SetValue(object id, string columnName, object value)
		{
			var cmd = string.Format("update [FBPages] set [{0}] = @Value where ID = @ID", columnName);
			
			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("Value", value ?? DBNull.Value), new SqlParameter("ID", id));
		}
		
		internal static void SafeDetach(object fkID, string columnName)
		{
			var cmd = string.Format("update [FBPages] set [{0}] = null where [{0}] = @FkID", columnName);

			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("FkID", fkID));
		}
	
	}
			
	
	public partial class Posts
	{

		#region Basic CRUD
		// Create / Update / Delete
		protected internal static int Insert(BO.Post post)
		{
			var cmd =  @"
				insert into [Posts]
					([Type], [Text], [AccountID], [PageID], [Sentiment], [ParentID], [ForeignID], [Date], [FromUserName], [SapID])
				values
					(@Type, @Text, @AccountID, @PageID, @Sentiment, @ParentID, @ForeignID, @Date, @FromUserName, @SapID);
				
				select scope_identity();
				";
				
			var parameters = new SqlParameter[10]; 
			
			parameters[0] = new SqlParameter("Type",post.Type );
			parameters[1] = new SqlParameter("Text",post.Text );
			if (post.AccountID == null)
				parameters[2] = new SqlParameter("AccountID",DBNull.Value);
			else
				parameters[2] = new SqlParameter("AccountID",post.AccountID);
			if (post.PageID == null)
				parameters[3] = new SqlParameter("PageID",DBNull.Value);
			else
				parameters[3] = new SqlParameter("PageID",post.PageID);
			if (post.Sentiment == null)
				parameters[4] = new SqlParameter("Sentiment",DBNull.Value);
			else
				parameters[4] = new SqlParameter("Sentiment",post.Sentiment);
			if (post.ParentID == null)
				parameters[5] = new SqlParameter("ParentID",DBNull.Value);
			else
				parameters[5] = new SqlParameter("ParentID",post.ParentID);
			parameters[6] = new SqlParameter("ForeignID",post.ForeignID );
			parameters[7] = new SqlParameter("Date",post.Date );
			parameters[8] = new SqlParameter("FromUserName",post.FromUserName );
			if (post.SapID == null)
				parameters[9] = new SqlParameter("SapID",DBNull.Value);
			else
				parameters[9] = new SqlParameter("SapID",post.SapID);
			
			return Convert.ToInt32(DBHelper.GetScalar(cmd, CommandType.Text, parameters));
		}
		
		protected internal static void Update(BO.Post post)
		{
			var cmd = @"
				update [Posts] set
					Type = @Type, Text = @Text, AccountID = @AccountID, PageID = @PageID, Sentiment = @Sentiment, ParentID = @ParentID, ForeignID = @ForeignID, Date = @Date, FromUserName = @FromUserName, SapID = @SapID
				where ID = @ID
				";
				
			var parameters = new SqlParameter[11];
			parameters[0] = new SqlParameter("ID",post.ID );
			parameters[1] = new SqlParameter("Type",post.Type );
			parameters[2] = new SqlParameter("Text",post.Text );
			if (post.AccountID == null)
				parameters[3] = new SqlParameter("AccountID",DBNull.Value);
			else
				parameters[3] = new SqlParameter("AccountID",post.AccountID);
			if (post.PageID == null)
				parameters[4] = new SqlParameter("PageID",DBNull.Value);
			else
				parameters[4] = new SqlParameter("PageID",post.PageID);
			if (post.Sentiment == null)
				parameters[5] = new SqlParameter("Sentiment",DBNull.Value);
			else
				parameters[5] = new SqlParameter("Sentiment",post.Sentiment);
			if (post.ParentID == null)
				parameters[6] = new SqlParameter("ParentID",DBNull.Value);
			else
				parameters[6] = new SqlParameter("ParentID",post.ParentID);
			parameters[7] = new SqlParameter("ForeignID",post.ForeignID );
			parameters[8] = new SqlParameter("Date",post.Date );
			parameters[9] = new SqlParameter("FromUserName",post.FromUserName );
			if (post.SapID == null)
				parameters[10] = new SqlParameter("SapID",DBNull.Value);
			else
				parameters[10] = new SqlParameter("SapID",post.SapID);
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, parameters);
		}
		
		protected internal static void Delete(object ID)
		{
			var cmd = "delete [Posts] where ID = @ID";
			
			DBHelper.ExecuteNonQuery(cmd, CommandType.Text, new SqlParameter("ID", ID));
		}

		#endregion 
			
		internal static DataTable Select(string cmd, SqlParameter[] parameters)
		{
			return DBHelper.GetTable(cmd, parameters);
		}
		
		internal static int Count(string cmd, SqlParameter[] parameters)
		{
			return Convert.ToInt32(DBHelper.GetScalar(cmd, parameters));
		}
		
		internal static void SetValue(object id, string columnName, object value)
		{
			var cmd = string.Format("update [Posts] set [{0}] = @Value where ID = @ID", columnName);
			
			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("Value", value ?? DBNull.Value), new SqlParameter("ID", id));
		}
		
		internal static void SafeDetach(object fkID, string columnName)
		{
			var cmd = string.Format("update [Posts] set [{0}] = null where [{0}] = @FkID", columnName);

			DBHelper.ExecuteNonQuery(cmd, new SqlParameter("FkID", fkID));
		}
	
	}
			
}



